package model.handlers;

import database.Database;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.SQLException;
import javax.xml.parsers.*;
import model.*;
import org.w3c.dom.*;
import org.xml.sax.SAXException;
import utility.Converter;

/**
 *
 * @author Sonny Sandberg
 */
public abstract class CustomerHandler 
{
    /**
     *  CREATE OR REPLACE VIEW getCustomerPhoneAddress AS
        (
                SELECT
                        c.id AS customerID,
                        c.name,
                        c.mortician,
                        ct.number,
                        a.id AS addressID,
                        a.street,
                        a.zip_code AS zipCode,
                        zp.city
                FROM
                        bs_customer AS c
                                INNER JOIN bs_customer_telephone AS ct ON c.id = ct.fk_customer
                                LEFT JOIN bs_customer_address AS ca ON c.id = ca.fk_customer
                                LEFT JOIN bs_address AS a ON a.id = ca.fk_address
                                LEFT JOIN bs_zip_code AS zp ON zp.zip_code = a.zip_code
        );
     * 
     * @param number
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public static CustomerSpecified[] getSpecifiedCustomersByNumber(String number) throws ClassNotFoundException, SQLException
    {
        CustomerSpecified[] customersSpecified = null;
        
        Database db = new Database();
        String[][] result = db.getFromDB("getCustomerPhoneAddress", "WHERE number = '"+number+"'", "", "", 
                                            "customerID", "name", "mortician", "number", "addressID", "street", "zipCode", "city");
        db.closeDB();
        
        customersSpecified = new CustomerSpecified[result.length];
        
        for (int i = 0; i < customersSpecified.length; i++)
        {
            CustomerName cName = new CustomerName(Integer.parseInt(result[i][0]), result[i][1], Converter.intToBoolean(Integer.parseInt(result[i][2])));
            
            Telephone telephone = new Telephone(result[i][3], cName);
            Address address = null;
            
            if (result[i][4] != null)
            {
                address = new Address(Integer.parseInt(result[i][4]), result[i][5], new ZipCode(result[i][6], result[i][7]));
            }
            
            if (address != null)
            {
                customersSpecified[i] = new CustomerSpecified(cName, telephone, address);
            }
            else
            {
                customersSpecified[i] = new CustomerSpecified(cName, telephone);
            }            
        }
        
        return customersSpecified;        
    }
    
    public static CustomerSpecified doKrakSearchByNumber(String number) 
            throws MalformedURLException, ParserConfigurationException, SAXException, IOException, SQLException, ClassNotFoundException
    {
        CustomerSpecified customersSpecified = null;
                
        URL url = new URL("http://basicservices.webservice.krak.dk/telesearch.asmx/GetTeleByTn?telephoneNumber="+number);
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
        Document doc = dBuilder.parse(url.openStream());

        doc.getDocumentElement().normalize();

        NodeList nodes = doc.getElementsByTagName("ArrayOfTele");
        Node node = nodes.item(0);                                         

        if (node.getNodeType() == Node.ELEMENT_NODE)
        {
            Element element = (Element) node;
           
            if (element != null && getXMLValue("FirstName", element).length() > 0)
            {
                CustomerName cName = new CustomerName(0, getXMLValue("FirstName", element)+" "+getXMLValue("LastName", element), false);
                Telephone telephone = new Telephone(number, cName);
                Address address = new Address();

                if (getXMLValue("PostalCode", element).length() > 0)
                {
                    ZipCode zipObj = AddressHandler.getCity(getXMLValue("PostalCode", element));
                    address.setZipCode(zipObj);
                }

                if (getXMLValue("RoadName", element).length() > 0)
                {
                    StringBuilder addressString = new StringBuilder();
                    addressString.append(getXMLValue("RoadName", element));
                    addressString.append(" "+getXMLValue("HouseNumberNumericFrom", element));
                    addressString.append(getXMLValue("HouseNumberCharacterFrom", element));
                    addressString.append(" "+getXMLValue("Floor", element));
                    addressString.append(" "+getXMLValue("Door", element));
                    address.setStreet(""+addressString);
                }
                

                customersSpecified = new CustomerSpecified(cName, telephone, address);                
            }
        }        
        
        return customersSpecified;
    }
    
    private static String getXMLValue(String tag, Element element)
    {
        String toReturn = "";
        if (element.getElementsByTagName(tag).item(0) != null)
        {
            NodeList nodes = element.getElementsByTagName(tag).item(0).getChildNodes();
            if (nodes.item(0) != null && nodes.item(0).getNodeValue().length() > 0)
            {
                Node node = (Node) nodes.item(0);
                toReturn = node.getNodeValue();
            }
        }
        
        return toReturn;
    }
    
    public static CustomerName getLastCustomerCreatedWithDetails(CustomerName customerName) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        
        String[][] result = db.getFromDB("bs_customer", "WHERE name = '"+customerName.getName()+"' AND mortician = '"+Converter.booleanToInt(customerName.isMortician())+"'", "ORDER BY id DESC", "LIMIT 1", "id");
        db.closeDB();
        
        if (result.length > 0)
        {
            customerName.setId(Integer.parseInt(result[0][0]));
        }
        
        return customerName;
    }
    
    /**
     *  CREATE OR REPLACE VIEW getCustomerPhoneAddress AS
        (
                SELECT
                        c.id AS customerID,
                        c.name,
                        c.mortician,
                        ct.number,
                        a.id AS addressID,
                        a.street,
                        a.zip_code AS zipCode,
                        zp.city
                FROM
                        bs_customer AS c
                                INNER JOIN bs_customer_telephone AS ct ON c.id = ct.fk_customer
                                LEFT JOIN bs_customer_address AS ca ON c.id = ca.fk_customer
                                LEFT JOIN bs_address AS a ON a.id = ca.fk_address
                                LEFT JOIN bs_zip_code AS zp ON zp.zip_code = a.zip_code
        );
     * 
     * @param int id
     * @return CustomerSpecified
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public static CustomerSpecified getCustomerSpecifiedByCustomerNameID(int id) throws ClassNotFoundException, SQLException
    {
        CustomerSpecified customersSpecified = null;
        
        Database db = new Database();
        
        String[][] result = db.getFromDB("getCustomerPhoneAddress", "WHERE customerID = "+id+"", "", "", 
                                            "customerID", "name", "mortician", "number", "addressID", "street", "zipCode", "city");
        db.closeDB();
        
        CustomerName cName = new CustomerName(Integer.parseInt(result[0][0]), result[0][1], Converter.intToBoolean(Integer.parseInt(result[0][2])));
            
        Telephone telephone = new Telephone(result[0][3], cName);
        Address address = null;

        if (result[0][4] != null)
        {
            address = new Address(Integer.parseInt(result[0][4]), result[0][5], new ZipCode(result[0][6], result[0][7]));
        }

        if (address != null)
        {
            customersSpecified = new CustomerSpecified(cName, telephone, address);
        }
        else
        {
            customersSpecified = new CustomerSpecified(cName, telephone);
        }
        
        return customersSpecified;        
    }
}